package flst.ipiweb.persistance.dao.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import flst.ipiweb.persistance.dao.IRelaiDao;
import flst.ipiweb.transverse.Adresse;
import flst.ipiweb.transverse.IPIException;
import flst.ipiweb.transverse.Relai;

public class RelaiDaoJdbc implements IRelaiDao {

	private JdbcUtils jdbcUtils = JdbcUtils.getInstance();

	@Override
	/**
	 * Cette méthode permet de créer un relai en base
	 * @param relai : le relai à créer
	 */
	public void createRelai(Relai relai) throws IPIException {
		Connection vConnection;
		try {
			vConnection = jdbcUtils.getConnection();
		
			String vRequeteInsert = "INSERT INTO IPII_DELIVERY_CENTERS (DELIVERY_CENTER_ID, DELIVERY_CENTER_NUMBER, DELIVERY_CENTER_NAME, ADDRESS_LINE_1, ADDRESS_LINE_2, ZIP_CODE, CITY, CREATION_DATE) "
									+"VALUES (SEQ_DELIVERY_CENTER.NEXTVAL, ?, ?, ?, ?, ?, ?, SYSDATE)";
			PreparedStatement vPs = vConnection.prepareStatement(vRequeteInsert);
			// DELIVERY_CENTER_NUM
			vPs.setInt(1, relai.getNumeroRelai());
			// DELIVERY_CENTER_NAME
			vPs.setString(2, relai.getNom());
			// ADDRESS_LINE_1
			vPs.setString(3, relai.getAdresse().getAdresse());
			// ADDRESS_LINE_2
			vPs.setString(4, relai.getAdresse().getComplementsAdresse());
			// ZIP_CODE
			vPs.setString(5, relai.getAdresse().getCodePostal());
			// CITY
			vPs.setString(6, relai.getAdresse().getVille());
			
			vPs.executeUpdate();

			vPs.close();
			vConnection.close();
		} catch (Exception e) {
			throw new IPIException("RelaiException.create.technique");
		}
	}

	@Override
	/**
	 * Cette méthode permet de supprimer un relai
	 * @param relai : le relai à supprimer
	 */
	public void deleteRelai(Relai relai) throws IPIException{
		try {
			Connection vConnection = jdbcUtils.getConnection();
			String vRequeteDelete = "DELETE FROM IPII_DELIVERY_CENTERS WHERE DELIVERY_CENTER_ID = ?";
			PreparedStatement vPs = vConnection.prepareStatement(vRequeteDelete);
			// relai_id
			vPs.setInt(1, relai.getIdRelai());
			vPs.executeUpdate();

			vPs.close();
			vConnection.close();
		} catch (Exception e) {
			throw new IPIException("RelaiException.delete.technique");
		}

	}

	@Override
	/**
	 * Cette méthode permet de générer un numéro de relai
	 * @return int : le numéro de relai généré
	 */
	public int genererNumRelai() {
		int numRelai = 0;
		try {
			Connection vConnection = jdbcUtils.getConnection();
			Statement vStmt = vConnection.createStatement();

			String vRequeteSelect = "SELECT nextval('SEQ_DELIVERY_CENTER_LINE') as NUM";
			vStmt.execute(vRequeteSelect);
			ResultSet vRs = vStmt.getResultSet();
			while (vRs.next()) {
				numRelai = vRs.getInt("NUM");
			}

			vStmt.close();
			vConnection.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return numRelai;
	}

	@Override
	/**
	 * Cette méthode permet de récupérer la liste des relais en base trié par numéro de relai
	 * @return List<Relai> : la liste des relais trouvés
	 */
	public List<Relai> listerRelai() throws IPIException{
		
		List<Relai> listeRelais = new ArrayList<Relai>();

		try {
			Connection vConnection = jdbcUtils.getConnection();
			Statement vStmt = vConnection.createStatement();

			String vRequeteSelect = "SELECT * FROM IPII_DELIVERY_CENTERS ORDER BY DELIVERY_CENTER_NUMBER";
			vStmt.execute(vRequeteSelect);
			ResultSet vRs = vStmt.getResultSet();
			while (vRs.next()) {
				Relai relai = new Relai();
				//DELIVERY_CENTER_ID 
				relai.setIdRelai(vRs.getInt("DELIVERY_CENTER_ID"));
				//DELIVERY_CENTER_NUMBER 
				relai.setNumeroRelai(vRs.getInt("DELIVERY_CENTER_NUMBER"));
				//DELIVERY_CENTER_NAME
				relai.setNom(vRs.getString("DELIVERY_CENTER_NAME"));
				Adresse adresse = new Adresse(); 
				//ADDRESS_LINE_1 
				adresse.setAdresse(vRs.getString("ADDRESS_LINE_1"));
				//ADDRESS_LINE_2
				adresse.setComplementsAdresse(vRs.getString("ADDRESS_LINE_2"));
				//ZIP_CODE 
				adresse.setCodePostal(vRs.getString("ZIP_CODE"));
				//CITY 
				adresse.setVille(vRs.getString("CITY"));
				relai.setAdresse(adresse);
				
				listeRelais.add(relai);
			}

			vStmt.close();
			vConnection.close();
		} catch (Exception e) {
			throw new IPIException("RelaiException.list.technique");
		}
		return listeRelais;
	}
	
	@Override
	/**
	 * Cette méthode permet de récupérer la liste des relais en base trié par nom de relai
	 * @return List<Relai> : la liste des relais trouvés
	 */
	public List<Relai> listerRelaiByName() throws IPIException {
		List<Relai> listeRelais = new ArrayList<Relai>();

		try {
			Connection vConnection = jdbcUtils.getConnection();
			Statement vStmt = vConnection.createStatement();

			String vRequeteSelect = "SELECT * FROM IPII_DELIVERY_CENTERS ORDER BY DELIVERY_CENTER_NAME";
			vStmt.execute(vRequeteSelect);
			ResultSet vRs = vStmt.getResultSet();
			while (vRs.next()) {
				Relai relai = new Relai();
				//DELIVERY_CENTER_ID 
				relai.setIdRelai(vRs.getInt("DELIVERY_CENTER_ID"));
				//DELIVERY_CENTER_NUMBER 
				relai.setNumeroRelai(vRs.getInt("DELIVERY_CENTER_NUMBER"));
				//DELIVERY_CENTER_NAME
				relai.setNom(vRs.getString("DELIVERY_CENTER_NAME"));
				Adresse adresse = new Adresse(); 
				//ADDRESS_LINE_1 
				adresse.setAdresse(vRs.getString("ADDRESS_LINE_1"));
				//ADDRESS_LINE_2
				adresse.setComplementsAdresse(vRs.getString("ADDRESS_LINE_2"));
				//ZIP_CODE 
				adresse.setCodePostal(vRs.getString("ZIP_CODE"));
				//CITY 
				adresse.setVille(vRs.getString("CITY"));
				relai.setAdresse(adresse);
				
				listeRelais.add(relai);
			}

			vStmt.close();
			vConnection.close();
		} catch (Exception e) {
			throw new IPIException("RelaiException.list.technique");
		}
		return listeRelais;
	}
	
	@Override
	/**
	 * Cette méthode permet de récupérer la liste des relais en base trié par ville
	 * @return List<Relai> : la liste des relais trouvés
	 */
	public List<Relai> listerRelaiByVille() throws IPIException {
		List<Relai> listeRelais = new ArrayList<Relai>();

		try {
			Connection vConnection = jdbcUtils.getConnection();
			Statement vStmt = vConnection.createStatement();

			String vRequeteSelect = "SELECT * FROM IPII_DELIVERY_CENTERS ORDER BY CITY";
			vStmt.execute(vRequeteSelect);
			ResultSet vRs = vStmt.getResultSet();
			while (vRs.next()) {
				Relai relai = new Relai();
				//DELIVERY_CENTER_ID 
				relai.setIdRelai(vRs.getInt("DELIVERY_CENTER_ID"));
				//DELIVERY_CENTER_NUMBER 
				relai.setNumeroRelai(vRs.getInt("DELIVERY_CENTER_NUMBER"));
				//DELIVERY_CENTER_NAME
				relai.setNom(vRs.getString("DELIVERY_CENTER_NAME"));
				Adresse adresse = new Adresse(); 
				//ADDRESS_LINE_1 
				adresse.setAdresse(vRs.getString("ADDRESS_LINE_1"));
				//ADDRESS_LINE_2
				adresse.setComplementsAdresse(vRs.getString("ADDRESS_LINE_2"));
				//ZIP_CODE 
				adresse.setCodePostal(vRs.getString("ZIP_CODE"));
				//CITY 
				adresse.setVille(vRs.getString("CITY"));
				relai.setAdresse(adresse);
				
				listeRelais.add(relai);
			}

			vStmt.close();
			vConnection.close();
		} catch (Exception e) {
			throw new IPIException("RelaiException.list.technique");
		}
		return listeRelais;
	}

	@Override
	/**
	 * Cette méthode permet de mettre à jour un relai en base
	 * @param relai : le relai à mettre à jour
	 */
	public void updateRelai(Relai relai) throws IPIException{
		
		try
		{
			Connection vConnection = jdbcUtils.getConnection();
			String vRequeteUpdate = "UPDATE IPII_DELIVERY_CENTERS SET DELIVERY_CENTER_NUMBER = ?, DELIVERY_CENTER_NAME = ?,ADDRESS_LINE_1 = ?,ADDRESS_LINE_2 =?,ZIP_CODE =?,CITY =? WHERE DELIVERY_CENTER_ID = ?";
			PreparedStatement vPs =	vConnection.prepareStatement(vRequeteUpdate) ;
			//LAST_NAME 
			vPs.setInt(1, relai.getNumeroRelai());
			//FIRST_NAME 
			vPs.setString(2,relai.getNom());
			//LOGIN
			vPs.setString(3, relai.getAdresse().getAdresse());
			//ENCRYPTED_PASSWORD
			vPs.setString(4, relai.getAdresse().getComplementsAdresse());			
			//ZIP_CODE
			vPs.setString(5, relai.getAdresse().getCodePostal());
			//CITY
			vPs.setString(6, relai.getAdresse().getVille());
			
			//DELIVERY_CENTER_ID
			vPs.setInt(7, relai.getIdRelai());

			vPs.executeUpdate();
			
			vPs.close();
			vConnection.close();
		}
		catch (Exception e) {
			throw new IPIException("RelaiException.update.technique");
		}
	}
	
	@Override
	/**
	 * Cette méthode permet de récupérer un relai par son identifiant
	 * @return relai : le relai trouvé
	 */
	public Relai getRelaiById(int idRelai) {
		Relai relai = new Relai();
		try
		{
			Connection vConnection = jdbcUtils.getConnection();
			
			String vRequeteSelect = "SELECT * FROM IPII_DELIVERY_CENTERS WHERE DELIVERY_CENTER_ID = ?";
			PreparedStatement vPs =	vConnection.prepareStatement(vRequeteSelect);
			//relai_id
			vPs.setInt(1,idRelai);
			
			vPs.execute();
			ResultSet vRs = vPs.getResultSet();
			
			vRs.next();
			
			//DELIVERY_CENTER_ID 
			relai.setIdRelai(vRs.getInt("DELIVERY_CENTER_ID"));
			//DELIVERY_CENTER_NUMBER 
			relai.setNumeroRelai(vRs.getInt("DELIVERY_CENTER_NUMBER"));
			//DELIVERY_CENTER_NAME
			relai.setNom(vRs.getString("DELIVERY_CENTER_NAME"));
			Adresse adresse = new Adresse(); 
			//ADDRESS_LINE_1 
			adresse.setAdresse(vRs.getString("ADDRESS_LINE_1"));
			//ADDRESS_LINE_2
			adresse.setComplementsAdresse(vRs.getString("ADDRESS_LINE_2"));
			//ZIP_CODE 
			adresse.setCodePostal(vRs.getString("ZIP_CODE"));
			//CITY 
			adresse.setVille(vRs.getString("CITY"));
			relai.setAdresse(adresse);
			
			vPs.close();
			vConnection.close();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return relai;
	}
}
